package com.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.bean.RecordBean;
import com.db.JdbcUtil;
import com.exception.DBException;
import com.exception.RenewException;

public class RenewDao
{
	private Connection con = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	public boolean ifReordExist(RecordBean rb) throws DBException,
			RenewException
	{
		String sql = "select recordID from lab_record where recordID=?";
		boolean flag = false;
		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			ps.setInt(1, rb.getRecordID());
			rs = ps.executeQuery();
			while (rs.next())
			{
				flag = true;
			}
		}
		catch (SQLException e)
		{
			throw new RenewException(
					"Fail to justify if the record exist or not!");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return flag;
	}

	public boolean ifRecordBack(RecordBean rb) throws DBException,
			RenewException
	{
		String sql = "select recordID from lab_record where recordID=? and backDate is null";
		boolean flag = false;
		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			ps.setInt(1, rb.getRecordID());
			rs = ps.executeQuery();
			while (rs.next())
			{
				flag = true;
			}
		}
		catch (SQLException e)
		{
			throw new RenewException(
					"Fail to justify if the record is back or not!");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return flag;
	}
	private Date CalculateBackDate(RecordBean rb) throws DBException, RenewException
	{
		Date sbackDate = null;
		String sql = "select sbackDate from lab_record where recordID=?";

		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			ps.setInt(1, rb.getRecordID());
			rs=ps.executeQuery();
			while(rs.next())
			{
				sbackDate = rs.getDate(1);
			}
			if(sbackDate == null)
			{
				throw new RenewException("Fail to get the date when user should back instrument!");
			}
		}
		catch (SQLException e)
		{
			throw new RenewException("Fail to get the date when user should back instrument!");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return sbackDate;
		
	}
	public void renewIns(RecordBean rb) throws DBException, RenewException
	{
		Date sbackDate = this.CalculateBackDate(rb);
		String sql = "update lab_record set sbackDate = date_add('"+sbackDate + "',interval "+ rb.getDays() +" day) where recordID=?";

		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			ps.setInt(1, rb.getRecordID());
			ps.executeUpdate();
		}
		catch (SQLException e)
		{
			throw new RenewException("Fail to renew the instrument!");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}

	}
	public boolean ifUserBorrow(RecordBean rb) throws DBException, RenewException
	{
		String sql = "select recordID from lab_record where userID=? and backDate is null";
		con = JdbcUtil.getConnection();
		boolean flag = false;
		try
		{
			// get the user info and user renew info
			ps = con.prepareStatement(sql);
			ps.setString(1, rb.getUserID());
			rs = ps.executeQuery();
			while (rs.next())
			{
				flag = true;
				break;
			}
		}
		catch (SQLException e)
		{
			throw new RenewException("Fail to justify the instrument user can renew!");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return flag;
	}
	public ArrayList<RecordBean> GetRenewRecord(RecordBean rb)
			throws DBException, RenewException
	{
		ArrayList<RecordBean> list = new ArrayList<RecordBean>();

		String sql = "select a.userID,d.barcode,a.firstname,a.lastname,a.gender,a.email,d.name,c.borrowDate,c.sbackDate,c.recordID from lab_user a join lab_record c on a.userID = c.userID  join lab_instrument d on d.barcode = c.barcode where a.userID=? and c.backDate is null";
		con = JdbcUtil.getConnection();
		try
		{
			// get the user info and user renew info
			ps = con.prepareStatement(sql);
			ps.setString(1, rb.getUserID());
			rs = ps.executeQuery();
			while (rs.next())
			{
				RecordBean recordBean = new RecordBean();
				recordBean.setUserID(rs.getString(1));
				recordBean.setBarcode(rs.getString(2));
				recordBean.setFirstname(rs.getString(3));
				recordBean.setLastname(rs.getString(4));
				recordBean.setGender(rs.getString(5));
				recordBean.setEmail(rs.getString(6));
				recordBean.setInsName(rs.getString(7));
				recordBean.setBorrowDate(rs.getDate(8));
				recordBean.setSBackDate(rs.getDate(9));
				recordBean.setRecordID(rs.getInt(10));
				list.add(recordBean);
			}
		}
		catch (SQLException e)
		{
			throw new RenewException("Fail to get record user can renew!");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return list;
	}

	public void getRenewResult(RecordBean recordBean) throws DBException,
			RenewException
	{
		String sql = "select a.userID,d.barcode,a.firstname,a.lastname,a.gender,a.email,d.name,c.borrowDate,c.sbackDate,c.recordID from lab_user a join lab_record c on a.userID = c.userID  join lab_instrument d on d.barcode = c.barcode where c.recordID=?";
		con = JdbcUtil.getConnection();
		try
		{
			ps = con.prepareStatement(sql);
			ps.setInt(1, recordBean.getRecordID());
			rs = ps.executeQuery();
			while (rs.next())
			{
				recordBean.setUserID(rs.getString(1));
				recordBean.setBarcode(rs.getString(2));
				recordBean.setFirstname(rs.getString(3));
				recordBean.setLastname(rs.getString(4));
				recordBean.setGender(rs.getString(5));
				recordBean.setEmail(rs.getString(6));
				recordBean.setInsName(rs.getString(7));
				recordBean.setBorrowDate(rs.getDate(8));
				recordBean.setSBackDate(rs.getDate(9));
			}
		}
		catch (SQLException e)
		{
			throw new RenewException("Fail to get renew result!");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
	}

}
